"""
TODO 查看SQL执行频率
1、查询当前会话sql执行类型的统计信息：
      show session status like 'com_______'; (7个_)

2、查看全局（自从上次MySQL服务器启动至今）执行类型的统计信息：
      show global status like  'com_______';

3、查看针对InnoDB引擎的统计信息：
      show status like 'InnoDB_row_%';

TODO 定位低效率执行SQL
定位方式：
   ① 慢查询日志
        查看慢查询日志配置信息：            show variables like '%slow_query_log%';
        开启慢查询日志：                  set global slow_query_log = 1;
        查看慢查询日志记录SQL的最低阈值时间：show variables like 'long_query_time%';
        修改慢查询日志记录SQL的最低阈值时间：set global long_query_time = 10;

   ② show processlist：查看MySQL在进行的线程（线程的状态、是否锁表等），实时查看SQL的执行状态，进行缩表操作的优化

TODO　Explain分析执行计划
基本使用：explain select * from emp where uid = 1;
     id：相同的表id相同，加载表的顺序是从左到右
         不同的表，id值越大，优先级越高，越先被执行

     select_type：simple简单表（不使用表连接或子查询）
                    explain select * from emp;
                    explain select * from emp,dept where emp.deptno = dept.deptno;

                 primary（主查询，外层的查询）和 subquery（子查询中的第一个select）
                    explain select * from role where rid = (select rid from user_role where uid = (select uid from user where uname = '小张'));

                 derived（临时表，union包含在from子句的子查询中，外层select被标记为derived）
                    explain select * from (select * from user limit 2)t;

                 union（第二个select出现在union之后）和 union result：从union表获取结果的select
                    explain select * from user where uid = 1 union select * from user where uid = 3;

     type：查询时使用的条件where后的情况，显示访问类型
           优先级：system > const > eq_ref > ref > range > index > all
             NULL:不访问任何表和索引，直接返回结果
                     explain select now();

             system:5.7版本以后，就没有system，而是all
             const:主键或唯一索引；被连接的部分是一个常量（const）值
             eq_ref:左表有主键索引，左表和右表的每一行一一对应
                     alter table emp add primary key(empid); -- 添加主键索引
                     explain select * from emp,dept where emp.deptno = dept.deptno;   eq_ref, all
             ref:
                    alter table emp drop primary key;
                    create index index_id on emp(empid); -- 添加普通索引
                    explain select * from emp,dept where emp.deptno = dept.deptno;   ref, all

             range:范围查询（where 后出现between、<、>等）
                    explain select * from emp where empid > 2;

             index:把索引列的全部数据都扫描
                    explain select empid from emp;

             all:全表扫描，id上没有索引
                    explain select * from emp,dept where emp.deptno = dept.deptno;  all, all

     possible_keys：可能使用的索引

     table：查询的表

     key：实际使用的索引（如：primary）

     key_len：索引字段的长度（如int为4个字节）

     rows：扫描多少行才能获取查询结果

     extra：执行情况的说明和描述
             using filesort:mysql会对数据使用一个外部的索引排序
                               show index from user;
                               explain select * from user order by uname; (user表只有uid这个主键索引，没有其他索引)

             using temporary:需要建立临时表（temporary table）来暂存中间结果，常用于order by 和 group by
                               explain select count(*) from user group by uname;

             using index:需要返回的所有列数据均在一颗索引树上
                               explain select uid,count(*) from user order by uid;

TODO show file分析SQL
1、作用：在做SQL优化时，了解时间都耗费在哪里去了

2、操作：
    查看MySQL是否支持profile：
        select @@have_profiling;

    开启profiling：
        set profiling = 1;

    查看SQL语句的耗时情况：
       show profiles;

    查看指定的SQL语句整个生命周期运行的每个环节耗时的详细情况：
       show profile for query 8;

    查看CPU的耗时情况：
      show profile cpu for query 88;  (all、cpu、block io、context switch、page faults等)
        status：sql语句执行的状态
        duration：sql执行过程中每一个步骤的耗时
        cpu_user：当前用户占有的cpu
        cpu_system：系统占有的cpu

TODO 查看trace优化器
1、作用：对SQL的跟踪，通过trace文件，可以进一步了解为什么优化器选择A计划，而不是B计划
2、操作：(用cmd操作）
    开启trace:
        set optimizer_trace = "enabled = on",end_markers_in_json = on;

    设置trace最大能够使用的内存大小，避免解析过程中因默认的内存过小而不能完整展示：
        set optimizer_trace_max_mem_size = 1,00,0000;

    执行SQL语句：
        select * from user a,user_role b,role c where a.uid = b.uid, b.rid = c.rid;

    检查information_schema.optimizer_trace，了解MySQL如何执行SQL：
        select * from information_schema.optimizer_trace\G;

TODO 索引优化
1、避免索引失效应用
    ① 全值匹配：索引生效，执行效率高

         创建组合索引：
           create index index_s on student(name,age,address);

         全值匹配：（where的字段和索引字段保持一致，顺序可以不一致）
           explain select * from student where name = 21 and age = 18,address = '云南';

    ② 最左前缀法则：查询从索引的最左列开始（name），不跳跃某一列（如只写name 和 address，跳过中间的age，只有name有效）
                  where后不要求顺序，但必须包含最左索引name
                    explain select * from student where age = 13 and name = '张三';

    ③ 其他匹配原则：范围查询最右边的列，不能使用索引
                     explain select * from student where name = '张三' and age > 21 and address = '北京';（address无效）

                  索引列进行运算操作，索引将失效
                     explain select * from student where substring(name,3,2) = '王小'； （name失效）

                  字符串不加单引号，索引将失效
                     explain select * from student where name = 李四；

                  尽量使用覆盖索引，避免select *
                     explain select * from student where name = '张三';        -- 需要从原表和磁盘上读取数据，效率低
                     explain select name from student where name = '张三';     -- 从索引树上就可以读取所有数据，效率高
                       注意：*处和where处的索引需保持一致，否则还是会从原表及磁盘读取

                  用or分割查询条件，or前后的索引都不会被用到
                     explain select * from student where name = '张三' or age = 13;

                  以%开头的like模糊查询，索引将失效
                     explain select * from student where name like '张%';  -- 用索引
                     explain select * from student where name like '%张';  -- 不用索引
                     explain select * from student where name like '%张%'; -- 不用索引

                      --> 弥补不足，不用*，使用索引列
                           explain select name from student where name like '%张';

                  MySQl评估使用索引比全表查询慢，就不用索引，由数据本身的特点决定
                       create index index_a on student(address);
                       explain select * from student where address = '北京'； -- 北京多，不用索引
                       explain select * from student where address = '西安'； -- 西安少，用索引

                  is NULL，is not NULL，谁多谁有效
                      create index index_n on student(nickname);
                      explain select * from student where nickname is NULL;     -- NUll少，索引有效
                      explain select * from student where nickname is not NULL; -- not NUll多，索引无效

                  普通索引：in用索引，not in 不用索引
                      explain select * from student where age in (19,20);     -- 用索引
                      explain select * from student where age not in (30,35); -- 不用索引

                  主键索引：in 和 not in 都用索引
                      explain select * from student where id in (1,2);     -- 用索引
                      explain select * from student where id not in (3,4); -- 用索引

                  单列索引和复合索引，尽量使用复合索引

TODO 大批量数据加载优化
1、主键有序优化

   插入log脚本文件：
      sql1 为主键有序
      sql2 为主键无序
      ，分割的为列，\n分割的为行
   ① 先检查全局系统变量local_infile的状态：
         show global variables like '%local_infile%';
   ② 开启local_infile：
        set global local_infile = 1;
   ③ 加载数据：
        load data local infile 'D:\\sql_data\\sql1.log' into table student fields terminated by ',' lines terminated by '\n';
        truncate table student;(清空表sql1，重新加载sql2)
        load data local infile 'D:\\sql_data\\sql2.log' into table student fields terminated by ',' lines terminated by '\n';
2、关闭唯一性校验
   ① 关闭唯一性校验
        set unique_checks = 0;
   ② 加载数据
        load data local infile 'D:\\sql_data\\sql1.log' into table student fields terminated by ',' lines terminated by '\n';
   ③ 开启唯一性校验
        set unique_checks = 1;

TODO insert优化
原始方法：
   inset into student values (1,'张三');
   inset into student values (3,'王五');
   inset into student values (2,'李四');

优化方案：
 ① 一次性插入：
      insert into student values (1,'张三'),(3,'王五'),(2,'李四');
 ② 在事务中进行数据插入：
      begin;
         inset into student values (1,'张三');
         inset into student values (3,'王五');
         inset into student values (2,'李四');
      commit;
 ③ 有序插入：
      inset into student values (1,'张三');
      inset into student values (2,'李四');
      inset into student values (3,'王五');

TODO order by优化
1、两种排序方式
    filesort：直接对返回数据进行排序
    using index：通过有序索引顺序扫描直接返回有序数据，即通过索引直接返回排序结果

      create index index_e(age,salary);    -- id 为主键索引

      explain select *             from student order by age;        -- using filesort
      explain select *             from student order by age,salary; -- using filesort

      explain select id            from student order by age;        -- using index
      explain select id,age        from student order by age;        -- using index
      explain select id,age,salary from student order by age;        -- using index
      explain select id,age,name   from student order by age;        -- using filesort

      explain select id,age       from student order by age asc,salary desc;  -- using index,using filesort
      explain select id,age       from student order by age asc,salary asc;  -- Backward index scan; using index
      explain select id,age       from student order by salary，age;  -- using index,using filesort
         注意：order by 后有多个排序字段时，要求尽量排序方式一致
              order by 后的多个排序字段顺序，尽量和索引字段顺序一致

     --> filesort 的优化
           ① 两种排序算法：
                两次扫描算法：根据条件取出排序字段和行指针信息，然后在排序区sort buffer 中排序，如果sort buffer不够，则在临时表temporary table
                           中存储排序结果，完成排序后，再根据指针回表读取记录，该操作可能会导致大量随机I/O操作
                一次扫描算法：一次性取出满足条件的所有字段，然后再排序区sort buffer中排序直接输出结果集，该操作内存开销较大，但效率更高
           ② 优化：提高相应值，尽量使用一次扫描算法
                show variables like 'max_length_for_sort_data';   -- 4096
                show variables like 'sort_buffer_size';           -- 262144
             通过set来设置。

TODO 子查询优化
1、原理：尽量使用连接join查询，因为不需要在内存中创建临时表来完成这个逻辑上需要两个步骤的查询工作
        子查询，需要在内存中创建临时表
2、操作：
    explain select * from emp,dept where emp.deptno = dept.deptno;
    explaiun select * from emp where deptno in (select deptno from dept);

TODO limit优化
优化一：在索引上完成排序分页操作，最后根据主键关联回原表查询所需要的其他列内容
   explain select * from student limit 90,0000 ,10;  -- 0.684
   explain select * from student a,(select id from student order by id limit 90,0000 ,10) b where a.id = b.id; -- 0.486

优化二：主键自增的表，把limit转换成某个位置的查询
   explain select * from student where id > 90,0000 limit 10;
"""